package dao;

import pojo.Book;
import util.DBHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDAO {

    private final String processTable = "book";
    private Connection c = null;    //默认此类中不关闭连接
    private PreparedStatement ps = null;
    private Statement s = null;
    private ResultSet rs = null;

    private static final String  url =  "/web_war_exploded/resources"; //统一这个路径存储资源(在out/artifact中)

    //查：获取总数
    public int getTotal() {
        int total = 0;
        try {
            c = DBHelper.getConnection();
            s = c.createStatement();
            String sql = "select count(*) from " + processTable;
            rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return total;
    }

    // 查：根据id获得详情
    public Book getBookById(int id) {
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where bk_id=?;"; // SQL语句
            ps = c.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("bk_id"));
                book.setName(rs.getString("bk_name"));
                book.setType(rs.getString("bk_type"));
                book.setAuthor(rs.getString("bk_author"));
                book.setPublisher(rs.getString("bk_publisher"));
                book.setReader(rs.getString("bk_reader"));
                book.setDiscuss(rs.getInt("bk_discuss"));
                book.setLike(rs.getInt("bk_like"));
                book.setCover(url + "/cover/" + rs.getString("bk_cover"));
                book.setBrief(rs.getString("bk_brief"));
                return book;
            } else {
                return null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            // 释放数据集对象
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            // 释放语句对象
            if (s != null) {
                try {
                    s.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    //查：指定区间对象
    public List<Book> list(int start, int count) {
        List<Book> books = new ArrayList<Book>();
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " order by bk_id limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("bk_id"));
                book.setName(rs.getString("bk_name"));
                book.setType(rs.getString("bk_type"));
                book.setAuthor(rs.getString("bk_author"));
                book.setPublisher(rs.getString("bk_publisher"));
                book.setReader(rs.getString("bk_reader"));
                book.setDiscuss(rs.getInt("bk_discuss"));
                book.setLike(rs.getInt("bk_like"));
                book.setCover(url + "/cover/" + rs.getString("bk_cover"));
                book.setBrief(rs.getString("bk_brief"));
                books.add(book);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return books;
    }

    //查：指定区间对象匹配类别
    public List<Book> listByCat(int start, int count, String category) {
        switch (category){
            case "hb" :
                category = "绘本";
                break;
            case "th" :
                category = "童话";
                break;
            case "xs" :
                category = "小说";
                break;
            case "mz" :
                category = "名著";
                break;
            default:
                category = "*";
        }
        List<Book> books = new ArrayList<Book>();
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where bk_type='" + category + "' order by bk_id limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("bk_id"));
                book.setName(rs.getString("bk_name"));
                book.setType(rs.getString("bk_type"));
                book.setAuthor(rs.getString("bk_author"));
                book.setPublisher(rs.getString("bk_publisher"));
                book.setReader(rs.getString("bk_reader"));
                book.setDiscuss(rs.getInt("bk_discuss"));
                book.setLike(rs.getInt("bk_like"));
                book.setCover(url + "/cover/" + rs.getString("bk_cover"));
                book.setBrief(rs.getString("bk_brief"));
                books.add(book);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return books;
    }

    //查：指定区间对象匹配关键字
    public List<Book> listByKey(int start, int count, String keyword) {
        List<Book> books = new ArrayList<Book>();
        try {
            c = DBHelper.getConnection();
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where bk_name like'%" + keyword + "%' order by bk_id limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("bk_id"));
                book.setName(rs.getString("bk_name"));
                book.setType(rs.getString("bk_type"));
                book.setAuthor(rs.getString("bk_author"));
                book.setPublisher(rs.getString("bk_publisher"));
                book.setReader(rs.getString("bk_reader"));
                book.setDiscuss(rs.getInt("bk_discuss"));
                book.setLike(rs.getInt("bk_like"));
                book.setCover(url + "/cover/" + rs.getString("bk_cover"));
                book.setBrief(rs.getString("bk_brief"));
                books.add(book);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return books;
    }

    //查：指定区间对象匹配类别、关键字
    public List<Book> list(int start, int count, String category, String keyword) {
        switch (category){
            case "hb" :
                category = "绘本";
                break;
            case "th" :
                category = "童话";
                break;
            case "xs" :
                category = "小说";
                break;
            case "mz" :
                category = "名著";
                break;
            default:
                category = "*";
        }
        List<Book> books = new ArrayList<Book>();
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where bk_type='" + category + "' and bk_name like '%" + keyword + "%' order by bk_id limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("bk_id"));
                book.setName(rs.getString("bk_name"));
                book.setType(rs.getString("bk_type"));
                book.setAuthor(rs.getString("bk_author"));
                book.setPublisher(rs.getString("bk_publisher"));
                book.setReader(rs.getString("bk_reader"));
                book.setDiscuss(rs.getInt("bk_discuss"));
                book.setLike(rs.getInt("bk_like"));
                book.setCover(url + "/cover/" + rs.getString("bk_cover"));
                book.setBrief(rs.getString("bk_brief"));
                books.add(book);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return books;
    }

    //更新：book的discuss
    public void updateDiscuss(Book book, int id) throws Exception {
        String sql = "update " + processTable + " set bk_discuss = ? where bk_id = ?";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, book.getDiscuss());
            ps.setInt(2, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps.close();
        }
    }

    //更新：book的like
    public void updateLike(Book book, int id) throws Exception {
        String sql = "update " + processTable + " set bk_like = ? where bk_id = ?";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, book.getLike());
            ps.setInt(2, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps.close();
        }
    }

}